#!/usr/bin/env bash
dt="`date -d "$d  0 days ago "  "+%Y-%m-%d"`"
mysql -ureadonly -preadonly123 -h192.168.0.49<<EOF
use jiayou;
select from_unixtime(created,'%Y-%m-%d'),enterprise_id,tactics_type,coupon_type,
sum(case when coupon_type = 1 or coupon_type = 3 then discount_amount else 0 end) as off_allmoney,
sum(case when coupon_type = 2 then discount_amount else 0 end) as discount_allmoney,

sum(case when (coupon_type = 1 or coupon_type = 3) and coupon_statue = 3 then actual_amoun else 0 end) as off_usedmoney,
sum(case when coupon_type = 2 and coupon_statue = 3 then actual_amoun else 0 end) as discount_usedmoney
from coupon_info where coupon_type !=99 and from_unixtime(created,'%Y-%m-%d')='$dt'
group by from_unixtime(created,'%Y-%m-%d'),enterprise_id,tactics_type,coupon_type ;
EOF